using System.ComponentModel.DataAnnotations.Schema;
using System.Data.SqlClient;
using System.Diagnostics;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using zijian666.Data;
using zijian666.Data.SqlElements;

namespace test1
{
    [TestClass]
    public class UnitTest1
    {
        static UnitTest1()
        {
            DbProviderFactories.RegisterFactory("System.Data.SqlClient", SqlClientFactory.Instance);
        }

        [TestMethod]
        public void SQLIn()
        {
            var conn = new SqlConnection();

            var command = (SqlCommand)conn.CreateCommand($"select * from table where id in ({new int[] { 1, 2, 3 }}) or id in ({new int[] { 4, 5, 6 }})");


            Console.WriteLine(command.CommandText);
            Assert.AreEqual("select * from table where id in (@p1, @p2, @p3) or id in (@p4, @p5, @p6)", command.CommandText);

            Assert.AreEqual(6, command.Parameters.Count);
            Assert.AreEqual(1, command.Parameters["p1"].Value);
            Assert.AreEqual(2, command.Parameters["p2"].Value);
            Assert.AreEqual(3, command.Parameters["p3"].Value);
            Assert.AreEqual(4, command.Parameters["p4"].Value);
            Assert.AreEqual(5, command.Parameters["p5"].Value);
            Assert.AreEqual(6, command.Parameters["p6"].Value);
        }

        [TestMethod]
        public void 嵌套SQL()
        {
            var code = "abc";
            var innerSQL = FormattableSql.Create($"select id from table where code = {code}");
            var conn = new SqlConnection();
            var command = (SqlCommand)conn.CreateCommand($"select * from table where id in ({innerSQL})");

            Console.WriteLine(command.CommandText);
            Assert.AreEqual($"select * from table where id in (select id from table where code = @p1)", command.CommandText);
            Assert.AreEqual(1, command.Parameters.Count);
            Assert.AreEqual(code, command.Parameters["p1"].Value);

        }

        [TestMethod]
        public void IsNull()
        {
            object value = DBNull.Value;
            var conn = new SqlConnection();
            var command = (SqlCommand)conn.CreateCommand($"select * from table where id {value:=}");
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("select * from table where id IS NULL", command.CommandText);
            Assert.AreEqual(0, command.Parameters.Count);
            value = Guid.NewGuid();

            var command1 = (SqlCommand)conn.CreateCommand($"select * from table where id {value:=}");
            Assert.IsNotNull(command1.CommandText);
            Assert.AreEqual($"select * from table where id = @p1", command1.CommandText);
            Assert.AreEqual(1, command1.Parameters.Count);
            Assert.AreEqual(value, command1.Parameters["p1"].Value);

        }


        [TestMethod]
        public void 测试各种操作()
        {
            /*
             #/! 数据库常量
             ##/!! 数据库对象名称
             : 数据库变量类型
             & 连接符
             =/<>/!= 比较符
             */
            Assert.AreEqual("a", new FormattableSql("{0:#}", ["a"]).SqlCommandText());
            Assert.AreEqual("[a]", new FormattableSql("{0:##}", ["a"]).SqlCommandText());
            Assert.AreEqual("a", new FormattableSql("{0:!}", ["a"]).SqlCommandText());
            Assert.AreEqual("[a]", new FormattableSql("{0:!!}", ["a"]).SqlCommandText());
            Assert.AreEqual("@p1", new FormattableSql("{0}", ["a"]).SqlCommandText());
            Assert.AreEqual("= @p1", new FormattableSql("{0:=}", ["a"]).SqlCommandText());
            Assert.AreEqual("IS NULL", new FormattableSql("{0:=}", [null]).SqlCommandText());
            Assert.AreEqual("!= @p1", new FormattableSql("{0:!=}", ["a"]).SqlCommandText());
            Assert.AreEqual("IS NOT NULL", new FormattableSql("{0:!=}", [null]).SqlCommandText());
            Assert.AreEqual("<> @p1", new FormattableSql("{0:<>}", ["a"]).SqlCommandText());
            Assert.AreEqual("IS NOT NULL", new FormattableSql("{0:<>}", [null]).SqlCommandText());


            Assert.AreEqual("@p1, @p2, @p3", new FormattableSql("{0}", [new object[] { "a", "b", "c" }]).SqlCommandText());
            Assert.AreEqual("a, b, c", new FormattableSql("{0:#}", [new object[] { "a", "b", "c" }]).SqlCommandText());
            Assert.AreEqual("@p1,@p2,@p3", new FormattableSql("{0:&,}", [new object[] { "a", "b", "c" }]).SqlCommandText());
            Assert.AreEqual("@p1,b,@p2", new FormattableSql("{0:&,}", [new object[] { "a", new FormatValue("b", "#"), "c" }]).SqlCommandText());
            Assert.AreEqual("@p1 AND @p2 AND @p3", new FormattableSql("{0:& AND }", [new object[] { "a", "b", "c" }]).SqlCommandText());

            {

                var command = new FormattableSql("{0}", [1]).SqlCommand();
                Assert.AreEqual("@p1", command.CommandText);
                Assert.AreEqual(1, command.Parameters.Count);
                Assert.AreEqual(DbType.Int32, command.Parameters["p1"].DbType);
            }
            {

                var command = new FormattableSql("{0::bigint}", [1]).SqlCommand();
                Assert.AreEqual("@p1", command.CommandText);
                Assert.AreEqual(1, command.Parameters.Count);
                Assert.AreEqual(DbType.Int64, command.Parameters["p1"].DbType);
            }
        }


        [TestMethod]
        public void 查询条件1()
        {
            var table = new SqlTable("table");
            var s1 = new SqlCondition { Column = new(table, "id"), Operator = SqlOperator.Equal, Value = [1] };

            var query = new SqlQuery()
            {
                Table = table,
                Select = new SqlSelect("", [new SqlColumn(table, "code")]),
                Where = new SqlWhere { Condition = s1 },
                OrderBy = default
            };
            var sql = query.ToSql()!;
            var cmd = sql.SqlCommand();
            Console.WriteLine(cmd.CommandText);

            Assert.AreEqual("SELECT [code] FROM [table] WHERE [id] = @p1", cmd.CommandText);
            Assert.AreEqual(1, cmd.Parameters.Count);
            Assert.AreEqual(s1.Value[0], cmd.Parameters["p1"].Value);

        }

        [TestMethod]
        public void 查询条件2()
        {
            var table = new SqlTable("table");
            var s1 = new SqlCondition { Column = new(table, "id"), Operator = SqlOperator.Equal, Value = [1] };
            var s2 = new SqlCondition { Column = new(table, "id"), Operator = SqlOperator.Equal, Value = [DBNull.Value] };
            var or = SqlCondition.Or([s1, s2]);

            var s3 = new SqlCondition { Column = new(table, "code"), Operator = SqlOperator.Like, Value = ["%a"] };
            var s4 = new SqlCondition { Column = new(table, "datetime"), Operator = SqlOperator.Between, Value = [DateTime.Now.AddDays(-1), DateTime.Now] };
            var and = SqlCondition.And([s3, s4]);

            var where = new SqlWhere(SqlCondition.And([or, and]));

            var query = new SqlQuery()
            {
                Table = table,
                Select = default,
                Where = where,
                OrderBy = default
            };
            var sql = query.ToSql()!;
            var cmd = sql.SqlCommand();
            Console.WriteLine(cmd.CommandText);
            Assert.AreEqual("SELECT * FROM [table] WHERE (([id] = @p1 OR [id] IS NULL) AND ([code] Like @p2 AND [datetime] BETWEEN @p3 AND @p4))", cmd.CommandText);
            Assert.AreEqual(4, cmd.Parameters.Count);
            Assert.AreEqual(s1.Value[0], cmd.Parameters["p1"].Value);
            Assert.AreEqual(s3.Value[0], cmd.Parameters["p2"].Value);
            Assert.AreEqual(s4.Value[0], cmd.Parameters["p3"].Value);
            Assert.AreEqual(s4.Value[1], cmd.Parameters["p4"].Value);
        }

        [TestMethod]
        public void 查询条件嵌套()
        {
            var table = new SqlTable("table");
            var s1 = new SqlCondition { Column = new(table, "id"), Operator = SqlOperator.Equal, Value = [1] };
            var s2 = new SqlCondition { Column = new(table, "id"), Operator = SqlOperator.Equal, Value = [DBNull.Value] };
            var or = SqlCondition.Or([s1, s2]);

            var s3 = new SqlCondition { Column = new(table, "code"), Operator = SqlOperator.Like, Value = ["%a"] };
            var s4 = new SqlCondition { Column = new(table, "datetime"), Operator = SqlOperator.Between, Value = [DateTime.Now.AddDays(-1), DateTime.Now] };
            var and = SqlCondition.And([s3, s4]);

            var where1 = SqlCondition.And([or, and]);


            var inner = (FormattableString)$"select 1 from table where {where1}";

            var root = new SqlCondition { Column = new(default, "datetime"), Operator = SqlOperator.Exists, Value = [inner] };

            var sql = (FormattableString)$"select * from table2 where {root}";

            var conn = new SqlConnection();
            var command = (SqlCommand)conn.CreateCommand(sql);

            Console.WriteLine(command.CommandText);
            Assert.AreEqual("select * from table2 where EXISTS (select 1 from table where (([id] = @p1 OR [id] IS NULL) AND ([code] Like @p2 AND [datetime] BETWEEN @p3 AND @p4)))", command.CommandText);

            Assert.AreEqual(4, command.Parameters.Count);
            Assert.AreEqual(s1.Value[0], command.Parameters["p1"].Value);
            Assert.AreEqual(s3.Value[0], command.Parameters["p2"].Value);
            Assert.AreEqual(s4.Value[0], command.Parameters["p3"].Value);
            Assert.AreEqual(s4.Value[1], command.Parameters["p4"].Value);


        }


        [TestMethod]
        public void 预编译查询条件实体()
        {
            var searcher = new UserSearcher
            {
                Id = 1,
                Name = "张三",
                Birthday = new object[] { DateTime.Now.AddYears(-20), DateTime.Now }
            };

            var query = searcher.Build().ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [user] WHERE ([id] = @p1 AND [name] Like @p2 AND [birthday] BETWEEN @p3 AND @p4)", command.CommandText);
            Assert.AreEqual(4, command.Parameters.Count);
            Assert.AreEqual(searcher.Id, command.Parameters["p1"].Value);
            Assert.AreEqual(searcher.Name, command.Parameters["p2"].Value);
            Assert.AreEqual(((object[])searcher.Birthday)[0], command.Parameters["p3"].Value);
            Assert.AreEqual(((object[])searcher.Birthday)[1], command.Parameters["p4"].Value);
        }

        [TestMethod]
        public void 预编译查询条件实体2()
        {
            var searcher = new UserSearcher
            {
                Id = 1,
                Name = null,
                Birthday = new object[] { DateTime.Now.AddYears(-20), DateTime.Now }
            };

            var query = searcher.Build().ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [user] WHERE ([id] = @p1 AND [birthday] BETWEEN @p2 AND @p3)", command.CommandText);
            Assert.AreEqual(3, command.Parameters.Count);
            Assert.AreEqual(searcher.Id, command.Parameters["p1"].Value);
            Assert.AreEqual(searcher.Birthday[0], command.Parameters["p2"].Value);
            Assert.AreEqual(searcher.Birthday[1], command.Parameters["p3"].Value);


        }

        [TestMethod]
        public void 预编译查询条件实体3()
        {
            var searcher = new UserSearcher
            {
                Ids = [1, 2, 3]
            };

            var query = searcher.Build().ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [user] WHERE [id] IN (@p1, @p2, @p3)", command.CommandText);
            Assert.AreEqual(3, command.Parameters.Count);
            Assert.AreEqual(searcher.Ids[0], command.Parameters["p1"].Value);
            Assert.AreEqual(searcher.Ids[1], command.Parameters["p2"].Value);
            Assert.AreEqual(searcher.Ids[2], command.Parameters["p3"].Value);


        }

        [TestMethod]
        public void 预编译查询条件实体3子查询()
        {
            var searcher = new UserSearcher
            {
                Id = 1,
                Name = "张三",
                Birthday = new object[] { DateTime.Now.AddYears(-20), DateTime.Now },
                TagId = new TagSearcher
                {
                    Code = "a",
                    Id = 1
                }
            };


            var query = searcher.Build().OrderBy("id desc,name").ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [user] WHERE ([id] = @p1 AND [name] Like @p2 AND [birthday] BETWEEN @p3 AND @p4 AND [tag_id] IN (SELECT [id] FROM [tag] WHERE ([id] = @p5 AND [code] = @p6))) ORDER BY [id] DESC ,[name]", command.CommandText);

            var sw = Stopwatch.StartNew();
            for (int i = 0; i < 10000; i++)
            {
                searcher.Build().OrderBy("id desc,name").ToSql();
            }
            sw.Stop();
            Console.WriteLine(sw.ElapsedMilliseconds);
            sw = Stopwatch.StartNew();
            for (int i = 0; i < 10000; i++)
            {
                query.SqlCommand();
            }
            sw.Stop();
            Console.WriteLine(sw.ElapsedMilliseconds);




        }

        [TestMethod]
        public void 预编译查询条件实体3空子查询()
        {
            var searcher = new UserSearcher
            {
                Id = 1,
                Name = "张三",
                Birthday = new object[] { DateTime.Now.AddYears(-20), DateTime.Now },
                TagId = new TagSearcher()
            };


            var query = searcher.Build().OrderBy("id desc,name").ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [user] WHERE ([id] = @p1 AND [name] Like @p2 AND [birthday] BETWEEN @p3 AND @p4) ORDER BY [id] DESC ,[name]", command.CommandText);




        }


        [TestMethod]
        public void 预编译查询条件实体4Exists查询()
        {
            var searcher = new UserSearcher
            {
                Tag = new TagSearcher()
                {
                    Id = 1,
                }
            };


            var query = searcher.Build().ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [user] WHERE EXISTS (SELECT [id] FROM [tag] WHERE [id] = @p1)", command.CommandText);
        }

        [TestMethod]
        public void 预编译查询条件实体5嵌套查询()
        {
            var searcher = new TagSearcher
            {
                Or = [
                    new TagSearcher(){ Id = 1, Code = "a" },
                    new TagSearcher(){ Id = 2, Code = "b" },
                ]
            };


            var query = searcher.Build().ToSql();
            var command = query.SqlCommand();
            Console.WriteLine(command.CommandText);
            Assert.AreEqual("SELECT * FROM [tag] WHERE (([id] = @p1 AND [code] = @p2) OR ([id] = @p3 AND [code] = @p4))", command.CommandText);




        }


        [TestMethod]
        public void 测试时间()
        {


        }

        //[TestMethod]
        //public void 预编译查询条件实体4()
        //{
        //    UserSearcher searcher = null!;
        //    var sql = searcher.OrderBy("id desc", ("name", true)).OrderBy(nameof(User.TagId)).Build();
        //    var conn = new SqlConnection();
        //    var command = (SqlCommand)conn.CreateCommand(sql);
        //    Console.WriteLine(command.CommandText);
        //    Assert.AreEqual("SELECT * FROM [user] ORDER BY [id] DESC, [name] ASC, [tag_id] ASC", command.CommandText);
        //    Assert.AreEqual(0, command.Parameters.Count); // 没有参数
        //}

        //[TestMethod]
        //public void FormatValue()
        //{
        //    var info = EntityScheam.Get<User>();
        //    var sql = FormattableSql.Create($"select {info[nameof(User.Birthday)]} from {info.Table} where {info[nameof(User.TagId)]} = {1}");

        //    var conn = new SqlConnection();
        //    var command = (SqlCommand)conn.CreateCommand(sql);
        //    Console.WriteLine(command.CommandText);
        //    Assert.AreEqual("select [birthday] from [user] where [tag_id] = @p1", command.CommandText);
        //    Assert.AreEqual(1, command.Parameters.Count);
        //    Assert.AreEqual(1, command.Parameters["p1"].Value);
        //}
    }


    class UserSearcher : ISqlSearcher<User>
    {
        private object? _name;

        [SqlOperator.Equal]
        public object? Id { get; set; }


        [SqlOperator.In(Column = nameof(Id))]
        public object[]? Ids { get; set; }

        [SqlOperator.Like]
        public object? Name { get => _name is null ? null : _name + "%"; set => _name = value; }

        [SqlOperator.Between]
        public object[]? Birthday { get; set; }

        [SqlOperator.In(SubSelect = [nameof(Tag.Id)])]
        public TagSearcher TagId { get; set; }


        [SqlOperator.Exists(SubSelect = [nameof(Tag.Id)])]
        public TagSearcher Tag { get; set; }
    }

    class TagSearcher : ISqlSearcher<Tag>
    {
        [SqlOperator.Equal]
        public object? Id { get; set; }
        [SqlOperator.Equal]
        public object? Code { get; set; }

        /// <summary>
        /// OR 条件
        /// </summary>
        [SqlOperator.Or]
        public ISqlSearcher<Tag>[]? Or { get; set; }

        /// <summary>
        ///  AND 条件
        /// </summary>
        [SqlOperator.Or]
        public ISqlSearcher<Tag>[]? And { get; set; }
    }

    [Table("user")]
    class User
    {
        /// <summary>
        /// 主键
        /// </summary>
        [Column("id")]
        public int Id { get; set; }
        /// <summary>
        /// 姓名
        /// </summary>
        [Column("name")]
        public string Name { get; set; }

        /// <summary>
        /// 生日
        /// </summary>
        [Column("birthday")]
        public DateTime Birthday { get; set; }

        [Column("tag_id")]
        public int TagId { get; set; }
    }

    [Table("tag")]
    class Tag
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("code")]
        public string Code { get; set; }
    }
}
